﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class PurchaseOrderInstallmentItemDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public PurchaseOrderInstallmentItemDAL()
        {

        }

        public IList<PurchaseOrderInstallmentItemInfo> GetPurchaseOrderInstallmentItem(int PoSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseOrderInstallmentItemInfo> infoList = new List<PurchaseOrderInstallmentItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@icm_po_seq", PoSeq);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT oi.*, v.ICM_VAT_SEQ"
                            + ", CAST(CONCAT(v.ICM_VAT_NAME, ' อัตราภาษี ', b.VAT_PERCENT , ' %') AS CHAR) AS ICM_VAT_NAME"
                            + " , COALESCE(i.ICM_RES_ITEM_NAME, x.BGM_EXP_NAME) AS ITEM_NAME "
                            + " , COALESCE(u.ICM_UNIT_NAME, 'รายการ') AS UNIT_NAME "
                        + " from ICM_PURCHASE_ORDER_INSTALLMENT_ITEM oi "
                            + " INNER JOIN ICM_PURCHASE_ORDER_INSTALLMENT o ON oi.ICM_PO_INSTALL_SEQ = o.ICM_PO_INSTALL_SEQ "
                            + " INNER JOIN ICM_BIDDER_QUOTATION q ON oi.ICM_BIDDER_QUO_SEQ = q.ICM_BIDDER_QUO_SEQ "
                            + " INNER JOIN ICM_BIDDER b ON q.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ "
                            + " INNER JOIN ICM_PURCHASE_REQUEST_ITEM ri ON q.ICM_PR_ITEM_SEQ = ri.ICM_PR_ITEM_SEQ "
                            + " INNER JOIN BGM_MAS_EXPENSE x ON ri.BGM_EXP_SEQ = x.BGM_EXP_SEQ "
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON ri.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ "
                            + " LEFT JOIN ICM_MAS_UNIT u ON i.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ "
                            + " INNER JOIN ICM_SYS_VAT v ON b.ICM_VAT_SEQ = v.ICM_VAT_SEQ "
                        + " WHERE o.ICM_PO_SEQ = @icm_po_seq"
                            + " AND q.SELECTED = @yes_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseOrderInstallmentItemInfo info = new PurchaseOrderInstallmentItemInfo();
                        PurchaseOrderInstallmentInfo infoPoInstalment = new PurchaseOrderInstallmentInfo();
                        BidderInfo infoBidder = new BidderInfo();
                        BidderQuotationInfo infoBidderQuotation = new BidderQuotationInfo();
                        SysVatInfo infoVat = new SysVatInfo();
                        PurchaseRequestItemInfo infoPrItem = new PurchaseRequestItemInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();
                        RecordInfo infoRecord = new RecordInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_PO_INSTALL_ITEM_SEQ"]);
                        infoVat.Seq = Convert.ToInt32(dr[i]["ICM_VAT_SEQ"]);
                        infoVat.Name = dr[i]["ICM_VAT_NAME"].ToString();
                        infoItem.Name = dr[i]["ITEM_NAME"].ToString();
                        info.Descr = dr[i]["ITEM_DESCR"].ToString();
                        info.Qty = Convert.ToInt32(dr[i]["ITEM_QTY"]);
                        infoUnit.Name = dr[i]["UNIT_NAME"].ToString();
                        info.PriceInVat = (Convert.IsDBNull(dr[i]["ITEM_PRICE_INCLUDE_VAT"]) ? null : (Nullable<Double>)(dr[i]["ITEM_PRICE_INCLUDE_VAT"]));
                        info.PriceExVat = (Convert.IsDBNull(dr[i]["ITEM_PRICE_EXCLUDE_VAT"]) ? null : (Nullable<Double>)(dr[i]["ITEM_PRICE_EXCLUDE_VAT"]));
                        info.ExVatAmount = (Convert.IsDBNull(dr[i]["EXCLUDE_VAT_AMOUNT"]) ? null : (Nullable<Double>)(dr[i]["EXCLUDE_VAT_AMOUNT"]));
                        info.VatAmount = (Convert.IsDBNull(dr[i]["VAT_AMOUNT"]) ? null : (Nullable<Double>)(dr[i]["VAT_AMOUNT"]));
                        info.NetAmount = (Convert.IsDBNull(dr[i]["NET_AMOUNT"]) ? null : (Nullable<Double>)(dr[i]["NET_AMOUNT"]));

                        infoBidder.InfoVat = infoVat;
                        infoBidderQuotation.InfoBidder = infoBidder;
                        infoItem.InfoUnit = infoUnit;
                        infoPrItem.InfoItem = infoItem;
                        infoBidderQuotation.InfoPrItem = infoPrItem;
                        info.InfoBidderQuotation = infoBidderQuotation;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PurchaseOrderInstallmentItemInfo> GetPurchaseOrderInstallmentItemFromInstallment(int install_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseOrderInstallmentItemInfo> infoList = new List<PurchaseOrderInstallmentItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@icm_po_install_seq", install_seq);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT oi.*, v.ICM_VAT_SEQ"
                            + ", CAST(CONCAT(v.ICM_VAT_NAME, ' อัตราภาษี ', b.VAT_PERCENT , ' %') AS CHAR) AS ICM_VAT_NAME"
                            + " , COALESCE(i.ICM_RES_ITEM_NAME, x.BGM_EXP_NAME) AS ITEM_NAME "
                            + " , COALESCE(u.ICM_UNIT_NAME, 'รายการ') AS UNIT_NAME "
                            + ", b.VAT_PERCENT"
                            + ", o.ICM_PO_INSTALL_NUMBER"
                        + " from ICM_PURCHASE_ORDER_INSTALLMENT_ITEM oi "
                            + " INNER JOIN ICM_PURCHASE_ORDER_INSTALLMENT o ON oi.ICM_PO_INSTALL_SEQ = o.ICM_PO_INSTALL_SEQ "
                            + " INNER JOIN ICM_BIDDER_QUOTATION q ON oi.ICM_BIDDER_QUO_SEQ = q.ICM_BIDDER_QUO_SEQ "
                            + " INNER JOIN ICM_BIDDER b ON q.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ "
                            + " INNER JOIN ICM_PURCHASE_REQUEST_ITEM ri ON q.ICM_PR_ITEM_SEQ = ri.ICM_PR_ITEM_SEQ "
                            + " INNER JOIN BGM_MAS_EXPENSE x ON ri.BGM_EXP_SEQ = x.BGM_EXP_SEQ "
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON ri.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ "
                            + " LEFT JOIN ICM_MAS_UNIT u ON i.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ "
                            + " INNER JOIN ICM_SYS_VAT v ON b.ICM_VAT_SEQ = v.ICM_VAT_SEQ "
                        + " WHERE o.ICM_PO_INSTALL_SEQ = @icm_po_install_seq"
                            + " AND q.SELECTED = @yes_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseOrderInstallmentItemInfo info = new PurchaseOrderInstallmentItemInfo();
                        PurchaseOrderInstallmentInfo infoPoInstalment = new PurchaseOrderInstallmentInfo();
                        BidderInfo infoBidder = new BidderInfo();
                        BidderQuotationInfo infoBidderQuotation = new BidderQuotationInfo();
                        SysVatInfo infoVat = new SysVatInfo();
                        PurchaseRequestItemInfo infoPrItem = new PurchaseRequestItemInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();
                        RecordInfo infoRecord = new RecordInfo();

                        infoPoInstalment.InstallNumber = Convert.ToInt32(dr[i]["ICM_PO_INSTALL_NUMBER"]);
                        info.Seq = Convert.ToInt32(dr[i]["ICM_PO_INSTALL_ITEM_SEQ"]);
                        infoVat.Seq = Convert.ToInt32(dr[i]["ICM_VAT_SEQ"]);
                        infoVat.Name = dr[i]["ICM_VAT_NAME"].ToString();
                        infoItem.Name = dr[i]["ITEM_NAME"].ToString();
                        info.Descr = dr[i]["ITEM_DESCR"].ToString();
                        info.Qty = Convert.ToInt32(dr[i]["ITEM_QTY"]);
                        infoUnit.Name = dr[i]["UNIT_NAME"].ToString();
                        info.PriceInVat = (Convert.IsDBNull(dr[i]["ITEM_PRICE_INCLUDE_VAT"]) ? null : (Nullable<Double>)(dr[i]["ITEM_PRICE_INCLUDE_VAT"]));
                        info.PriceExVat = (Convert.IsDBNull(dr[i]["ITEM_PRICE_EXCLUDE_VAT"]) ? null : (Nullable<Double>)(dr[i]["ITEM_PRICE_EXCLUDE_VAT"]));
                        info.ExVatAmount = (Convert.IsDBNull(dr[i]["EXCLUDE_VAT_AMOUNT"]) ? null : (Nullable<Double>)(dr[i]["EXCLUDE_VAT_AMOUNT"]));
                        info.VatAmount = (Convert.IsDBNull(dr[i]["VAT_AMOUNT"]) ? null : (Nullable<Double>)(dr[i]["VAT_AMOUNT"]));
                        info.NetAmount = (Convert.IsDBNull(dr[i]["NET_AMOUNT"]) ? null : (Nullable<Double>)(dr[i]["NET_AMOUNT"]));
                        infoBidder.VatPercent = (Convert.IsDBNull(dr[i]["VAT_PERCENT"]) ? null : (Nullable<Double>)(dr[i]["VAT_PERCENT"]));

                        infoBidder.InfoVat = infoVat;
                        infoBidderQuotation.InfoBidder = infoBidder;
                        infoItem.InfoUnit = infoUnit;
                        infoPrItem.InfoItem = infoItem;
                        infoBidderQuotation.InfoPrItem = infoPrItem;
                        info.InfoBidderQuotation = infoBidderQuotation;
                        info.InfoPoInstallment = infoPoInstalment;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PurchaseOrderInstallmentItemInfo> GetBidderQuotationFromBidder(int BidderSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseOrderInstallmentItemInfo> infoList = new List<PurchaseOrderInstallmentItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@icm_bidder_seq", BidderSeq);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT q.ICM_BIDDER_QUO_SEQ"
                            + ", v.ICM_VAT_SEQ"
                            + ", CAST(CONCAT(v.ICM_VAT_NAME, ' อัตราภาษี ', b.VAT_PERCENT , ' %') AS CHAR) AS ICM_VAT_NAME"
                            + ", COALESCE(i.ICM_RES_ITEM_NAME, x.BGM_EXP_NAME) AS ITEM_NAME"
                            + ", q.ITEM_DESCR"
                            + ", q.ITEM_QTY"
                            + ", COALESCE(u.ICM_UNIT_NAME, 'รายการ') AS UNIT_NAME"
                            + ", CASE WHEN b.ICM_VAT_SEQ = 3 THEN (q.NET_AMOUNT / q.ITEM_QTY)"
                                + " ELSE ROUND(((q.NET_AMOUNT / q.ITEM_QTY) * (100/(b.VAT_PERCENT + 100))), 3)"
                            + " END AS ITEM_PRICE_EXCLUDE_VAT"
                            + ", CASE WHEN b.ICM_VAT_SEQ = 3 THEN (q.NET_AMOUNT / q.ITEM_QTY)"
                                + " ELSE ROUND((q.NET_AMOUNT / q.ITEM_QTY), 3)"
                            + " END AS ITEM_PRICE_INCLUDE_VAT"
                            + ", ROUND((q.NET_AMOUNT * (100/(b.VAT_PERCENT + 100))), 3) AS EXCLUDE_VAT_AMOUNT"
                            + ", ROUND((q.NET_AMOUNT * (b.VAT_PERCENT/(b.VAT_PERCENT + 100))), 3) AS VAT_AMOUNT"
                            + ", q.NET_AMOUNT"
                        + " FROM ICM_BIDDER_QUOTATION q "
                            + " INNER JOIN ICM_BIDDER b ON q.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ "
                            + " INNER JOIN ICM_PURCHASE_REQUEST_ITEM ri ON q.ICM_PR_ITEM_SEQ = ri.ICM_PR_ITEM_SEQ "
                            + " INNER JOIN BGM_MAS_EXPENSE x ON ri.BGM_EXP_SEQ = x.BGM_EXP_SEQ "
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON ri.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ "
                            + " LEFT JOIN ICM_MAS_UNIT u ON i.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ "
                            + " INNER JOIN ICM_SYS_VAT v ON b.ICM_VAT_SEQ = v.ICM_VAT_SEQ "
                        + " WHERE q.ICM_BIDDER_SEQ = @icm_bidder_seq"
                            + " AND q.SELECTED = @yes_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseOrderInstallmentItemInfo info = new PurchaseOrderInstallmentItemInfo();
                        BidderQuotationInfo infoBidderQuotation = new BidderQuotationInfo();
                        PurchaseRequestItemInfo infoPrItem = new PurchaseRequestItemInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();
                        SysVatInfo infoVat = new SysVatInfo();
                        BidderInfo infoBidder = new BidderInfo();

                        infoBidderQuotation.Seq = Convert.ToInt32(dr[i]["ICM_BIDDER_QUO_SEQ"]);
                        infoVat.Seq = Convert.ToInt32(dr[i]["ICM_VAT_SEQ"]);
                        infoVat.Name = dr[i]["ICM_VAT_NAME"].ToString();
                        infoItem.Name = dr[i]["ITEM_NAME"].ToString();
                        infoUnit.Name = dr[i]["UNIT_NAME"].ToString();
                        info.Descr = dr[i]["ITEM_DESCR"].ToString();
                        info.Qty = (Convert.IsDBNull(dr[i]["ITEM_QTY"]) ? null : (Nullable<int>)(dr[i]["ITEM_QTY"]));
                        info.PriceExVat = (Convert.IsDBNull(dr[i]["ITEM_PRICE_EXCLUDE_VAT"]) ? null : (Nullable<double>)(dr[i]["ITEM_PRICE_EXCLUDE_VAT"]));
                        info.PriceInVat = (Convert.IsDBNull(dr[i]["ITEM_PRICE_INCLUDE_VAT"]) ? null : (Nullable<double>)(dr[i]["ITEM_PRICE_INCLUDE_VAT"]));
                        info.ExVatAmount = (Convert.IsDBNull(dr[i]["EXCLUDE_VAT_AMOUNT"]) ? null : (Nullable<double>)(dr[i]["EXCLUDE_VAT_AMOUNT"]));
                        info.VatAmount = (Convert.IsDBNull(dr[i]["VAT_AMOUNT"]) ? null : (Nullable<double>)(dr[i]["VAT_AMOUNT"]));
                        info.NetAmount = (Convert.IsDBNull(dr[i]["NET_AMOUNT"]) ? null : (Nullable<double>)(dr[i]["NET_AMOUNT"]));

                        infoItem.InfoUnit = infoUnit;
                        infoPrItem.InfoItem = infoItem;
                        infoBidder.InfoVat = infoVat;
                        infoBidderQuotation.InfoPrItem = infoPrItem;
                        infoBidderQuotation.InfoBidder = infoBidder;
                        info.InfoBidderQuotation = infoBidderQuotation;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PurchaseOrderInstallmentItemInfo> GetBidderQuotationFromPurchaseOrder(int po_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseOrderInstallmentItemInfo> infoList = new List<PurchaseOrderInstallmentItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@icm_po_seq", po_seq);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT q.ICM_BIDDER_QUO_SEQ"
                            + ", v.ICM_VAT_SEQ"
                            + ", CAST(CONCAT(v.ICM_VAT_NAME, ' อัตราภาษี ', b.VAT_PERCENT , ' %') AS CHAR) AS ICM_VAT_NAME"
                            + ", COALESCE(i.ICM_RES_ITEM_NAME, x.BGM_EXP_NAME) AS ITEM_NAME"
                            + ", q.ITEM_DESCR"
                            + ", q.ITEM_QTY"
                            + ", COALESCE(u.ICM_UNIT_NAME, 'รายการ') AS UNIT_NAME"
                            + ", CASE WHEN b.ICM_VAT_SEQ = 3 THEN (q.NET_AMOUNT / q.ITEM_QTY)"
                                + " ELSE ROUND(((q.NET_AMOUNT / q.ITEM_QTY) * (100/(b.VAT_PERCENT + 100))), 3)"
                            + " END AS ITEM_PRICE_EXCLUDE_VAT"
                            + ", CASE WHEN b.ICM_VAT_SEQ = 3 THEN (q.NET_AMOUNT / q.ITEM_QTY)"
                                + " ELSE ROUND((q.NET_AMOUNT / q.ITEM_QTY), 3)"
                            + " END AS ITEM_PRICE_INCLUDE_VAT"
                            + ", ROUND((q.NET_AMOUNT * (100/(b.VAT_PERCENT + 100))), 3) AS EXCLUDE_VAT_AMOUNT"
                            + ", ROUND((q.NET_AMOUNT * (b.VAT_PERCENT/(b.VAT_PERCENT + 100))), 3) AS VAT_AMOUNT"
                            + ", q.NET_AMOUNT"
                        + " FROM ICM_PURCHASE_ORDER po"
                            + " INNER JOIN ICM_BIDDER b ON b.ICM_BIDDER_SEQ = po.ICM_BIDDER_SEQ "
                            + " INNER JOIN ICM_BIDDER_QUOTATION q ON b.ICM_BIDDER_SEQ = q.ICM_BIDDER_SEQ"
                            + " INNER JOIN ICM_PURCHASE_REQUEST_ITEM ri ON q.ICM_PR_ITEM_SEQ = ri.ICM_PR_ITEM_SEQ "
                            + " INNER JOIN BGM_MAS_EXPENSE x ON ri.BGM_EXP_SEQ = x.BGM_EXP_SEQ "
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON ri.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ "
                            + " LEFT JOIN ICM_MAS_UNIT u ON i.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ "
                            + " INNER JOIN ICM_SYS_VAT v ON b.ICM_VAT_SEQ = v.ICM_VAT_SEQ "
                        + " WHERE po.ICM_PO_SEQ = @icm_po_seq"
                            + " AND q.SELECTED = @yes_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseOrderInstallmentItemInfo info = new PurchaseOrderInstallmentItemInfo();
                        BidderQuotationInfo infoBidderQuotation = new BidderQuotationInfo();
                        PurchaseRequestItemInfo infoPrItem = new PurchaseRequestItemInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();
                        SysVatInfo infoVat = new SysVatInfo();
                        BidderInfo infoBidder = new BidderInfo();

                        infoBidderQuotation.Seq = Convert.ToInt32(dr[i]["ICM_BIDDER_QUO_SEQ"]);
                        infoVat.Seq = Convert.ToInt32(dr[i]["ICM_VAT_SEQ"]);
                        infoVat.Name = dr[i]["ICM_VAT_NAME"].ToString();
                        infoItem.Name = dr[i]["ITEM_NAME"].ToString();
                        infoUnit.Name = dr[i]["UNIT_NAME"].ToString();
                        info.Descr = dr[i]["ITEM_DESCR"].ToString();
                        info.Qty = (Convert.IsDBNull(dr[i]["ITEM_QTY"]) ? null : (Nullable<int>)(dr[i]["ITEM_QTY"]));
                        info.PriceExVat = (Convert.IsDBNull(dr[i]["ITEM_PRICE_EXCLUDE_VAT"]) ? null : (Nullable<double>)(dr[i]["ITEM_PRICE_EXCLUDE_VAT"]));
                        info.PriceInVat = (Convert.IsDBNull(dr[i]["ITEM_PRICE_INCLUDE_VAT"]) ? null : (Nullable<double>)(dr[i]["ITEM_PRICE_INCLUDE_VAT"]));
                        info.ExVatAmount = (Convert.IsDBNull(dr[i]["EXCLUDE_VAT_AMOUNT"]) ? null : (Nullable<double>)(dr[i]["EXCLUDE_VAT_AMOUNT"]));
                        info.VatAmount = (Convert.IsDBNull(dr[i]["VAT_AMOUNT"]) ? null : (Nullable<double>)(dr[i]["VAT_AMOUNT"]));
                        info.NetAmount = (Convert.IsDBNull(dr[i]["NET_AMOUNT"]) ? null : (Nullable<double>)(dr[i]["NET_AMOUNT"]));

                        infoItem.InfoUnit = infoUnit;
                        infoPrItem.InfoItem = infoItem;
                        infoBidder.InfoVat = infoVat;
                        infoBidderQuotation.InfoPrItem = infoPrItem;
                        infoBidderQuotation.InfoBidder = infoBidder;
                        info.InfoBidderQuotation = infoBidderQuotation;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PurchaseOrderInstallmentItemInfo> GetBidderQuotationFromBidderForConclusion(int BidderSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseOrderInstallmentItemInfo> infoList = new List<PurchaseOrderInstallmentItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@icm_bidder_seq", BidderSeq);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT SUM(COALESCE(q.ITEM_QTY,0)) AS ITEM_QTY"
                            + ", COALESCE(i.ICM_RES_ITEM_NAME, x.BGM_EXP_NAME) AS ITEM_NAME"
                            + ", COALESCE(u.ICM_UNIT_NAME, 'รายการ') AS UNIT_NAME"
                            + ", SUM(COALESCE(q.NET_AMOUNT,0)) AS NET_AMOUNT"
                            + ", ri.ICM_PR_ITEM_SEQ"
                        + " FROM ICM_BIDDER_QUOTATION q "
                            + " INNER JOIN ICM_BIDDER b ON q.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ "
                            + " INNER JOIN ICM_PURCHASE_REQUEST_ITEM ri ON q.ICM_PR_ITEM_SEQ = ri.ICM_PR_ITEM_SEQ "
                            + " INNER JOIN BGM_MAS_EXPENSE x ON ri.BGM_EXP_SEQ = x.BGM_EXP_SEQ "
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON ri.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ "
                            + " LEFT JOIN ICM_MAS_UNIT u ON i.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ "
                            + " INNER JOIN ICM_SYS_VAT v ON b.ICM_VAT_SEQ = v.ICM_VAT_SEQ "
                        + " WHERE q.ICM_BIDDER_SEQ = @icm_bidder_seq"
                            + " AND q.SELECTED = @yes_status"
                        + " GROUP BY COALESCE(i.ICM_RES_ITEM_NAME, x.BGM_EXP_NAME)"
                            + ", COALESCE(u.ICM_UNIT_NAME, 'รายการ')"
                            + ", ri.ICM_PR_ITEM_SEQ";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseOrderInstallmentItemInfo info = new PurchaseOrderInstallmentItemInfo();
                        BidderQuotationInfo infoBidderQuotation = new BidderQuotationInfo();
                        PurchaseRequestItemInfo infoPrItem = new PurchaseRequestItemInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();

                        infoItem.Name = dr[i]["ITEM_NAME"].ToString()
                            + " จำนวน "
                            + (Convert.IsDBNull(dr[i]["ITEM_QTY"]) ? "" : Convert.ToInt32(dr[i]["ITEM_QTY"]).ToString("###,###,##0"))
                            + " " + dr[i]["UNIT_NAME"].ToString()
                            + " วงเงิน "
                            + (Convert.IsDBNull(dr[i]["NET_AMOUNT"]) ? "" : Convert.ToDouble(dr[i]["NET_AMOUNT"]).ToString("###,###,##0"))
                            + " บาท";
                        infoPrItem.Seq = (Convert.IsDBNull(dr[i]["ICM_PR_ITEM_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PR_ITEM_SEQ"]));

                        infoPrItem.InfoItem = infoItem;
                        infoBidderQuotation.InfoPrItem = infoPrItem;
                        info.InfoBidderQuotation = infoBidderQuotation;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PurchaseOrderInstallmentItemInfo> GetPurchaseOrderInstallmentItemWithConclusion(int po_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseOrderInstallmentItemInfo> infoList = new List<PurchaseOrderInstallmentItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@icm_po_seq", po_seq);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT oi.*, v.ICM_VAT_SEQ"
                            + ", CAST(CONCAT(v.ICM_VAT_NAME, ' อัตราภาษี ', b.VAT_PERCENT , ' %') AS CHAR) AS ICM_VAT_NAME"
                            + ", COALESCE(i.ICM_RES_ITEM_NAME, x.BGM_EXP_NAME) AS ITEM_NAME "
                            + ", COALESCE(u.ICM_UNIT_NAME, 'รายการ') AS UNIT_NAME "
                            + ", (select sum(item_qty) from icm_bidder_quotation where icm_bidder_seq = b.icm_bidder_seq and icm_pr_item_seq = ri.icm_pr_item_seq and selected = @yes_status) AS BIDDER_QTY"
                            + ", (select sum(net_amount) from icm_bidder_quotation where icm_bidder_seq = b.icm_bidder_seq and icm_pr_item_seq = ri.icm_pr_item_seq and selected = @yes_status) AS BIDDER_NET_AMOUNT"
                            + ", ri.ICM_PR_ITEM_SEQ"
                            + ", o.ICM_PO_INSTALL_NUMBER"
                        + " from ICM_PURCHASE_ORDER_INSTALLMENT_ITEM oi "
                            + " INNER JOIN ICM_PURCHASE_ORDER_INSTALLMENT o ON oi.ICM_PO_INSTALL_SEQ = o.ICM_PO_INSTALL_SEQ "
                            + " INNER JOIN ICM_BIDDER_QUOTATION q ON oi.ICM_BIDDER_QUO_SEQ = q.ICM_BIDDER_QUO_SEQ "
                            + " INNER JOIN ICM_BIDDER b ON q.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ "
                            + " INNER JOIN ICM_PURCHASE_REQUEST_ITEM ri ON q.ICM_PR_ITEM_SEQ = ri.ICM_PR_ITEM_SEQ "
                            + " INNER JOIN BGM_MAS_EXPENSE x ON ri.BGM_EXP_SEQ = x.BGM_EXP_SEQ "
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON ri.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ "
                            + " LEFT JOIN ICM_MAS_UNIT u ON i.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ "
                            + " INNER JOIN ICM_SYS_VAT v ON b.ICM_VAT_SEQ = v.ICM_VAT_SEQ "
                        + " WHERE o.ICM_PO_SEQ = @icm_po_seq"
                            + " AND q.SELECTED = @yes_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseOrderInstallmentItemInfo info = new PurchaseOrderInstallmentItemInfo();
                        BidderQuotationInfo infoBidderQuotation = new BidderQuotationInfo();
                        PurchaseRequestItemInfo infoPrItem = new PurchaseRequestItemInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();
                        SysVatInfo infoVat = new SysVatInfo();
                        BidderInfo infoBidder = new BidderInfo();
                        PurchaseOrderInstallmentInfo infoInstallment = new PurchaseOrderInstallmentInfo();

                        infoItem.Name = dr[i]["ITEM_NAME"].ToString()
                            + " จำนวน "
                            + (Convert.IsDBNull(dr[i]["BIDDER_QTY"]) ? "" : Convert.ToInt32(dr[i]["BIDDER_QTY"]).ToString("###,###,##0"))
                            + " " + dr[i]["UNIT_NAME"].ToString()
                            + " วงเงิน "
                            + (Convert.IsDBNull(dr[i]["BIDDER_NET_AMOUNT"]) ? "" : Convert.ToDouble(dr[i]["BIDDER_NET_AMOUNT"]).ToString("###,###,##0"))
                            + " บาท"
                            + " (ฐานภาษี : "
                            + dr[i]["ICM_VAT_NAME"].ToString()
                            + ")";

                        infoPrItem.Seq = (Convert.IsDBNull(dr[i]["ICM_PR_ITEM_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PR_ITEM_SEQ"]));
                        infoInstallment.InstallNumber = (Convert.IsDBNull(dr[i]["ICM_PO_INSTALL_NUMBER"]) ? null : (Nullable<int>)(dr[i]["ICM_PO_INSTALL_NUMBER"]));
                        infoBidderQuotation.Seq = Convert.ToInt32(dr[i]["ICM_BIDDER_QUO_SEQ"]);
                        infoVat.Seq = Convert.ToInt32(dr[i]["ICM_VAT_SEQ"]);
                        infoVat.Name = dr[i]["ICM_VAT_NAME"].ToString();
                        infoUnit.Name = dr[i]["UNIT_NAME"].ToString();
                        info.Descr = dr[i]["ITEM_DESCR"].ToString();
                        info.Qty = (Convert.IsDBNull(dr[i]["ITEM_QTY"]) ? null : (Nullable<int>)(dr[i]["ITEM_QTY"]));
                        info.PriceExVat = (Convert.IsDBNull(dr[i]["ITEM_PRICE_EXCLUDE_VAT"]) ? null : (Nullable<double>)(dr[i]["ITEM_PRICE_EXCLUDE_VAT"]));
                        info.PriceInVat = (Convert.IsDBNull(dr[i]["ITEM_PRICE_INCLUDE_VAT"]) ? null : (Nullable<double>)(dr[i]["ITEM_PRICE_INCLUDE_VAT"]));
                        info.ExVatAmount = (Convert.IsDBNull(dr[i]["EXCLUDE_VAT_AMOUNT"]) ? null : (Nullable<double>)(dr[i]["EXCLUDE_VAT_AMOUNT"]));
                        info.VatAmount = (Convert.IsDBNull(dr[i]["VAT_AMOUNT"]) ? null : (Nullable<double>)(dr[i]["VAT_AMOUNT"]));
                        info.NetAmount = (Convert.IsDBNull(dr[i]["NET_AMOUNT"]) ? null : (Nullable<double>)(dr[i]["NET_AMOUNT"]));

                        infoItem.InfoUnit = infoUnit;
                        infoPrItem.InfoItem = infoItem;
                        infoBidder.InfoVat = infoVat;
                        infoBidderQuotation.InfoPrItem = infoPrItem;
                        infoBidderQuotation.InfoBidder = infoBidder;
                        info.InfoBidderQuotation = infoBidderQuotation;
                        info.InfoPoInstallment = infoInstallment;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PurchaseOrderInstallmentItemInfo> GetBidderQuotationFromBidderForInstallment(int BidderSeq, int install_number)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseOrderInstallmentItemInfo> infoList = new List<PurchaseOrderInstallmentItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@icm_bidder_seq", BidderSeq);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT q.ICM_BIDDER_QUO_SEQ"
                            + ", v.ICM_VAT_SEQ"
                            + ", CAST(CONCAT(v.ICM_VAT_NAME, ' อัตราภาษี ', b.VAT_PERCENT , ' %') AS CHAR) AS ICM_VAT_NAME"
                            + ", b.VAT_PERCENT"
                            + ", COALESCE(i.ICM_RES_ITEM_NAME, x.BGM_EXP_NAME) AS ITEM_NAME"
                            + ", q.ITEM_DESCR"
                            + ", 0 AS ITEM_QTY"
                            + ", COALESCE(u.ICM_UNIT_NAME, 'รายการ') AS UNIT_NAME"
                            + ", CASE WHEN b.ICM_VAT_SEQ = 3 THEN (q.NET_AMOUNT / q.ITEM_QTY)"
                                + " ELSE ROUND(((q.NET_AMOUNT / q.ITEM_QTY) * (100/(b.VAT_PERCENT + 100))), 3)"
                            + " END AS ITEM_PRICE_EXCLUDE_VAT"
                            + ", CASE WHEN b.ICM_VAT_SEQ = 3 THEN (q.NET_AMOUNT / q.ITEM_QTY)"
                                + " ELSE ROUND((q.NET_AMOUNT / q.ITEM_QTY), 3)"
                            + " END AS ITEM_PRICE_INCLUDE_VAT"
                            + ", 0 AS EXCLUDE_VAT_AMOUNT"
                            + ", 0 AS VAT_AMOUNT"
                            + ", 0 AS NET_AMOUNT"
                            + ", ri.ICM_PR_ITEM_SEQ"
                        + " FROM ICM_BIDDER_QUOTATION q "
                            + " INNER JOIN ICM_BIDDER b ON q.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ "
                            + " INNER JOIN ICM_PURCHASE_REQUEST_ITEM ri ON q.ICM_PR_ITEM_SEQ = ri.ICM_PR_ITEM_SEQ "
                            + " INNER JOIN BGM_MAS_EXPENSE x ON ri.BGM_EXP_SEQ = x.BGM_EXP_SEQ "
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON ri.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ "
                            + " LEFT JOIN ICM_MAS_UNIT u ON i.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ "
                            + " INNER JOIN ICM_SYS_VAT v ON b.ICM_VAT_SEQ = v.ICM_VAT_SEQ "
                        + " WHERE q.ICM_BIDDER_SEQ = @icm_bidder_seq"
                            + " AND q.SELECTED = @yes_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseOrderInstallmentItemInfo info = new PurchaseOrderInstallmentItemInfo();
                        PurchaseOrderInstallmentInfo infoInstallment = new PurchaseOrderInstallmentInfo();
                        BidderQuotationInfo infoBidderQuotation = new BidderQuotationInfo();
                        PurchaseRequestItemInfo infoPrItem = new PurchaseRequestItemInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();
                        SysVatInfo infoVat = new SysVatInfo();
                        BidderInfo infoBidder = new BidderInfo();

                        infoInstallment.InstallNumber = install_number;
                        infoBidderQuotation.Seq = Convert.ToInt32(dr[i]["ICM_BIDDER_QUO_SEQ"]);
                        infoVat.Seq = Convert.ToInt32(dr[i]["ICM_VAT_SEQ"]);
                        infoVat.Name = dr[i]["ICM_VAT_NAME"].ToString();
                        infoBidder.VatPercent = (Convert.IsDBNull(dr[i]["VAT_PERCENT"]) ? null : (Nullable<double>)(dr[i]["VAT_PERCENT"]));
                        infoItem.Name = dr[i]["ITEM_NAME"].ToString();
                        infoUnit.Name = dr[i]["UNIT_NAME"].ToString();
                        info.Descr = dr[i]["ITEM_DESCR"].ToString();
                        info.Qty = (Convert.IsDBNull(dr[i]["ITEM_QTY"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["ITEM_QTY"]));
                        info.PriceExVat = (Convert.IsDBNull(dr[i]["ITEM_PRICE_EXCLUDE_VAT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["ITEM_PRICE_EXCLUDE_VAT"]));
                        info.PriceInVat = (Convert.IsDBNull(dr[i]["ITEM_PRICE_INCLUDE_VAT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["ITEM_PRICE_INCLUDE_VAT"]));
                        info.ExVatAmount = (Convert.IsDBNull(dr[i]["EXCLUDE_VAT_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["EXCLUDE_VAT_AMOUNT"]));
                        info.VatAmount = (Convert.IsDBNull(dr[i]["VAT_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["VAT_AMOUNT"]));
                        info.NetAmount = (Convert.IsDBNull(dr[i]["NET_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["NET_AMOUNT"]));
                        infoPrItem.Seq = (Convert.IsDBNull(dr[i]["ICM_PR_ITEM_SEQ"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["ICM_PR_ITEM_SEQ"]));

                        infoItem.InfoUnit = infoUnit;
                        infoPrItem.InfoItem = infoItem;
                        infoBidder.InfoVat = infoVat;
                        infoBidderQuotation.InfoPrItem = infoPrItem;
                        infoBidderQuotation.InfoBidder = infoBidder;
                        info.InfoBidderQuotation = infoBidderQuotation;
                        info.InfoPoInstallment = infoInstallment;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PurchaseOrderInstallmentItemInfo> GetBidderQuotationFromPurchaseOrderForInstallment(int po_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseOrderInstallmentItemInfo> infoList = new List<PurchaseOrderInstallmentItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@icm_po_seq", po_seq);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT q.ICM_BIDDER_QUO_SEQ"
                            + ", v.ICM_VAT_SEQ"
                            + ", CAST(CONCAT(v.ICM_VAT_NAME, ' อัตราภาษี ', b.VAT_PERCENT , ' %') AS CHAR) AS ICM_VAT_NAME"
                            + ", b.VAT_PERCENT"
                            + ", COALESCE(i.ICM_RES_ITEM_NAME, x.BGM_EXP_NAME) AS ITEM_NAME"
                            + ", q.ITEM_DESCR"
                            + ", 0 AS ITEM_QTY"
                            + ", COALESCE(u.ICM_UNIT_NAME, 'รายการ') AS UNIT_NAME"
                            + ", CASE WHEN b.ICM_VAT_SEQ = 3 THEN (q.NET_AMOUNT / q.ITEM_QTY)"
                                + " ELSE ROUND(((q.NET_AMOUNT / q.ITEM_QTY) * (100/(b.VAT_PERCENT + 100))), 3)"
                            + " END AS ITEM_PRICE_EXCLUDE_VAT"
                            + ", CASE WHEN b.ICM_VAT_SEQ = 3 THEN (q.NET_AMOUNT / q.ITEM_QTY)"
                                + " ELSE ROUND((q.NET_AMOUNT / q.ITEM_QTY), 3)"
                            + " END AS ITEM_PRICE_INCLUDE_VAT"
                            + ", 0 AS EXCLUDE_VAT_AMOUNT"
                            + ", 0 AS VAT_AMOUNT"
                            + ", 0 AS NET_AMOUNT"
                            + ", ri.ICM_PR_ITEM_SEQ"
                        + " FROM ICM_PURCHASE_ORDER po"
                            + " INNER JOIN ICM_BIDDER b ON po.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ "
                            + " INNER JOIN ICM_BIDDER_QUOTATION q ON q.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ"
                            + " INNER JOIN ICM_PURCHASE_REQUEST_ITEM ri ON q.ICM_PR_ITEM_SEQ = ri.ICM_PR_ITEM_SEQ "
                            + " INNER JOIN BGM_MAS_EXPENSE x ON ri.BGM_EXP_SEQ = x.BGM_EXP_SEQ "
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON ri.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ "
                            + " LEFT JOIN ICM_MAS_UNIT u ON i.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ "
                            + " INNER JOIN ICM_SYS_VAT v ON b.ICM_VAT_SEQ = v.ICM_VAT_SEQ "
                        + " WHERE po.ICM_PO_SEQ = @icm_po_seq"
                            + " AND q.SELECTED = @yes_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseOrderInstallmentItemInfo info = new PurchaseOrderInstallmentItemInfo();
                        PurchaseOrderInstallmentInfo infoInstallment = new PurchaseOrderInstallmentInfo();
                        BidderQuotationInfo infoBidderQuotation = new BidderQuotationInfo();
                        PurchaseRequestItemInfo infoPrItem = new PurchaseRequestItemInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();
                        SysVatInfo infoVat = new SysVatInfo();
                        BidderInfo infoBidder = new BidderInfo();

                        infoBidderQuotation.Seq = Convert.ToInt32(dr[i]["ICM_BIDDER_QUO_SEQ"]);
                        infoVat.Seq = Convert.ToInt32(dr[i]["ICM_VAT_SEQ"]);
                        infoVat.Name = dr[i]["ICM_VAT_NAME"].ToString();
                        infoBidder.VatPercent = (Convert.IsDBNull(dr[i]["VAT_PERCENT"]) ? null : (Nullable<double>)(dr[i]["VAT_PERCENT"]));
                        infoItem.Name = dr[i]["ITEM_NAME"].ToString();
                        infoUnit.Name = dr[i]["UNIT_NAME"].ToString();
                        info.Descr = dr[i]["ITEM_DESCR"].ToString();
                        info.Qty = (Convert.IsDBNull(dr[i]["ITEM_QTY"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["ITEM_QTY"]));
                        info.PriceExVat = (Convert.IsDBNull(dr[i]["ITEM_PRICE_EXCLUDE_VAT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["ITEM_PRICE_EXCLUDE_VAT"]));
                        info.PriceInVat = (Convert.IsDBNull(dr[i]["ITEM_PRICE_INCLUDE_VAT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["ITEM_PRICE_INCLUDE_VAT"]));
                        info.ExVatAmount = (Convert.IsDBNull(dr[i]["EXCLUDE_VAT_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["EXCLUDE_VAT_AMOUNT"]));
                        info.VatAmount = (Convert.IsDBNull(dr[i]["VAT_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["VAT_AMOUNT"]));
                        info.NetAmount = (Convert.IsDBNull(dr[i]["NET_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["NET_AMOUNT"]));
                        infoPrItem.Seq = (Convert.IsDBNull(dr[i]["ICM_PR_ITEM_SEQ"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["ICM_PR_ITEM_SEQ"]));
                        infoPrItem.ItemName = dr[i]["ITEM_NAME"].ToString();

                        infoItem.InfoUnit = infoUnit;
                        infoPrItem.InfoItem = infoItem;
                        infoBidder.InfoVat = infoVat;
                        infoBidderQuotation.InfoPrItem = infoPrItem;
                        infoBidderQuotation.InfoBidder = infoBidder;
                        info.InfoBidderQuotation = infoBidderQuotation;
                        info.InfoPoInstallment = infoInstallment;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public void Insert(IList<PurchaseOrderInstallmentItemInfo> infoList, Nullable<int> install_seq, PurchaseOrderInfo info, IDBManager dbManagerTrans)
        {
            string strSQL;
            try
            {
                for (int i = 0 ; i < infoList.Count ; i++)
                {
                    dbManagerTrans.CreateParameters(11);

                    dbManagerTrans.AddParameters(0, "@icm_po_install_seq", install_seq);
                    dbManagerTrans.AddParameters(1, "@icm_bidder_quo_seq", infoList[i].InfoBidderQuotation.Seq);
                    dbManagerTrans.AddParameters(2, "@item_descr", infoList[i].Descr);
                    dbManagerTrans.AddParameters(3, "@item_qty", infoList[i].Qty);
                    dbManagerTrans.AddParameters(4, "@item_price_exclude_vat", infoList[i].PriceExVat);
                    dbManagerTrans.AddParameters(5, "@item_price_include_vat", infoList[i].PriceInVat);
                    dbManagerTrans.AddParameters(6, "@exclude_vat_amount", infoList[i].ExVatAmount);
                    dbManagerTrans.AddParameters(7, "@vat_amount", infoList[i].VatAmount);
                    dbManagerTrans.AddParameters(8, "@net_amount", infoList[i].NetAmount);
                    dbManagerTrans.AddParameters(9, "@created_by", info.InfoRecord.Created_by);
                    dbManagerTrans.AddParameters(10, "@created_when", info.InfoRecord.Created_when);

                    strSQL = "INSERT INTO ICM_PURCHASE_ORDER_INSTALLMENT_ITEM("
                                + "ICM_PO_INSTALL_SEQ"
                                + ", ICM_BIDDER_QUO_SEQ"
                                + ", ITEM_DESCR"
                                + ", ITEM_QTY"
                                + ", ITEM_PRICE_EXCLUDE_VAT"
                                + ", ITEM_PRICE_INCLUDE_VAT"
                                + ", EXCLUDE_VAT_AMOUNT"
                                + ", VAT_AMOUNT"
                                + ", NET_AMOUNT"
                                + ", CREATED_BY"
                                + ", CREATED_WHEN"
                                + ") "
                            + " VALUES (@icm_po_install_seq"
                                + ", @icm_bidder_quo_seq"
                                + ", @item_descr"
                                + ", @item_qty"
                                + ", @item_price_exclude_vat"
                                + ", @item_price_include_vat"
                                + ", @exclude_vat_amount"
                                + ", @vat_amount"
                                + ", @net_amount"
                                + ", @created_by"
                                + ", @created_when"
                                + ")";

                    dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
                }
            }
            catch
            {
                throw;
            }
        }

        public void Insert(IList<PurchaseOrderInstallmentItemInfo> infoList, Nullable<int> install_seq, PurchaseOrderInstallmentInfo info, IDBManager dbManagerTrans)
        {
            string strSQL;
            try
            {
                for (int i = 0; i < infoList.Count; i++)
                {
                    dbManagerTrans.CreateParameters(11);

                    dbManagerTrans.AddParameters(0, "@icm_po_install_seq", install_seq);
                    dbManagerTrans.AddParameters(1, "@icm_bidder_quo_seq", infoList[i].InfoBidderQuotation.Seq);
                    dbManagerTrans.AddParameters(2, "@item_descr", infoList[i].Descr);
                    dbManagerTrans.AddParameters(3, "@item_qty", infoList[i].Qty);
                    dbManagerTrans.AddParameters(4, "@item_price_exclude_vat", infoList[i].PriceExVat);
                    dbManagerTrans.AddParameters(5, "@item_price_include_vat", infoList[i].PriceInVat);
                    dbManagerTrans.AddParameters(6, "@exclude_vat_amount", infoList[i].ExVatAmount);
                    dbManagerTrans.AddParameters(7, "@vat_amount", infoList[i].VatAmount);
                    dbManagerTrans.AddParameters(8, "@net_amount", infoList[i].NetAmount);
                    dbManagerTrans.AddParameters(9, "@created_by", info.InfoRecord.Created_by);
                    dbManagerTrans.AddParameters(10, "@created_when", info.InfoRecord.Created_when);

                    strSQL = "INSERT INTO ICM_PURCHASE_ORDER_INSTALLMENT_ITEM("
                                + "ICM_PO_INSTALL_SEQ"
                                + ", ICM_BIDDER_QUO_SEQ"
                                + ", ITEM_DESCR"
                                + ", ITEM_QTY"
                                + ", ITEM_PRICE_EXCLUDE_VAT"
                                + ", ITEM_PRICE_INCLUDE_VAT"
                                + ", EXCLUDE_VAT_AMOUNT"
                                + ", VAT_AMOUNT"
                                + ", NET_AMOUNT"
                                + ", CREATED_BY"
                                + ", CREATED_WHEN"
                                + ") "
                            + " VALUES (@icm_po_install_seq"
                                + ", @icm_bidder_quo_seq"
                                + ", @item_descr"
                                + ", @item_qty"
                                + ", @item_price_exclude_vat"
                                + ", @item_price_include_vat"
                                + ", @exclude_vat_amount"
                                + ", @vat_amount"
                                + ", @net_amount"
                                + ", @created_by"
                                + ", @created_when"
                                + ")";

                    dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
                }
            }
            catch
            {
                throw;
            }
        }

        public void Update(IList<PurchaseOrderInstallmentItemInfo> infoList)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                for (int i = 0; i < infoList.Count; i++)
                {
                    dbManager.CreateParameters(10);

                    dbManager.AddParameters(0, "@seq", infoList[i].Seq);
                    dbManager.AddParameters(1, "@item_descr", infoList[i].Descr);
                    dbManager.AddParameters(2, "@item_qty", infoList[i].Qty);
                    dbManager.AddParameters(3, "@item_price_exclude_vat", infoList[i].PriceExVat);
                    dbManager.AddParameters(4, "@item_price_include_vat", infoList[i].PriceInVat);
                    dbManager.AddParameters(5, "@exclude_vat_amount", infoList[i].ExVatAmount);
                    dbManager.AddParameters(6, "@vat_amount", infoList[i].VatAmount);
                    dbManager.AddParameters(7, "@net_amount", infoList[i].NetAmount);
                    dbManager.AddParameters(8, "@updated_by", infoList[i].InfoRecord.Updated_by);
                    dbManager.AddParameters(9, "@updated_when", infoList[i].InfoRecord.Updated_when);

                    strSQL = "UPDATE ICM_PURCHASE_ORDER_INSTALLMENT_ITEM"
                            + " SET ITEM_DESCR = @item_descr"
                            + ", ITEM_QTY = @item_qty"
                            + ", ITEM_PRICE_EXCLUDE_VAT = @item_price_exclude_vat"
                            + ", ITEM_PRICE_INCLUDE_VAT = @item_price_include_vat"
                            + ", EXCLUDE_VAT_AMOUNT = @exclude_vat_amount"
                            + ", VAT_AMOUNT = @vat_amount"
                            + ", NET_AMOUNT = @net_amount"
                            + ", UPDATED_BY = @updated_by"
                            + ", UPDATED_WHEN = @updated_when"
                            + " WHERE ICM_PO_INSTALL_ITEM_SEQ =@seq";

                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                }
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                return;
            }
        }

        public void Insert(PurchaseOrderInstallmentItemInfo info)
        {
            string strSQL;
            try
            {
                dbManager.CreateParameters(11);

                dbManager.AddParameters(0, "@icm_po_instal_seq", info.InfoPoInstallment.Seq);
                dbManager.AddParameters(1, "@icm_bidder_quo_seq", info.InfoBidderQuotation.Seq);
                dbManager.AddParameters(2, "@item_descr", info.Descr);
                dbManager.AddParameters(3, "@item_qty", info.Qty);
                dbManager.AddParameters(4, "@item_price_exclude_vat", info.PriceExVat);
                dbManager.AddParameters(5, "@item_price_include_vat", info.PriceInVat);
                dbManager.AddParameters(6, "@exclude_vat_amount", info.ExVatAmount);
                dbManager.AddParameters(7, "@vat_amount", info.VatAmount);
                dbManager.AddParameters(8, "@net_amount", info.NetAmount);
                dbManager.AddParameters(9, "@created_by", info.InfoRecord.Created_by);
                dbManager.AddParameters(10, "@created_when", info.InfoRecord.Created_when);

                strSQL = "INSERT INTO ICM_PURCHASE_ORDER_INSTALLMENT_ITEM ("
                            + "ICM_PO_INSTALL_SEQ"
                            + ", ICM_BIDDER_QUO_SEQ"
                            + ", ITEM_DESCR"
                            + ", ITEM_QTY"
                            + ", ITEM_PRICE_EXCLUDE_VAT"
                            + ", ITEM_PRICE_INCLUDE_VAT"
                            + ", EXCLUDE_VAT_AMOUNT"
                            + ", VAT_AMOUNT"
                            + ", NET_AMOUNT"
                            + ", CREATED_BY"
                            + ", CREATED_WHEN"
                            + ") "
                        + " VALUES (@icm_po_install_seq"
                            + ", @icm_bidder_quo_seq"
                            + ", @item_descr"
                            + ", @item_qty"
                            + ", @item_price_exclude_amount"
                            + ", @item_price_include_amount"
                            + ", @exclude_vat_amount"
                            + ", @vat_amount"
                            + ", @net_amount"
                            + ", @created_by"
                            + ", @created_when"
                            + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
